package com.logisticsmanagement.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.logisticsmanagement.domain.DeliveryStation;
import com.logisticsmanagement.utils.DataSource;
import com.logisticsmanagement.utils.DatabaseUtil;

public class DeliveryStationDao {
	/**
 	 * query information of a delivery station by deliveryStationID
	 * @param id
	 * @return
	 */
	public DeliveryStation selectById(Integer id){

		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		DeliveryStation deliveryStation = null;
		
		try {
			conn= DataSource.getConnection();   //get connection to database
			if(conn == null)
				return null;
			sta = conn.prepareStatement(	 
					"select * from DeliveryStation where deliveryStationID=? ");	 //get operation statement object of database 
			sta.setInt(1, id);
			rs = sta.executeQuery();	 //query database, return ResultSet
			if(rs.next()){
				deliveryStation =  new DeliveryStation();
				deliveryStation.setDeliveryStationID(id);
				deliveryStation.setCityID(rs.getInt(2));
				deliveryStation.setManagerID(rs.getString(3));
				deliveryStation.setStationName(rs.getString(4));
				deliveryStation.setArea(rs.getString(5));
				deliveryStation.setPrepareDelete(rs.getBoolean(6));
				deliveryStation.setIsDelete(rs.getBoolean(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return deliveryStation;
	}
	
	/**
	 * add deliveryStation
	 * @param deliveryStation
	 */
	public Boolean add(DeliveryStation deliveryStation){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to object
			if(conn == null)
				return null;
			sta = conn.prepareStatement(
					"insert into DeliveryStation(deliveryStationID,cityID,"
					+ "managerID,stationName,area,prepareDelete,isDelete) "
					+ "values(?,?,?,?,?,?,?) ");	 //get operation statement object of database
			sta.setInt(1, deliveryStation.getDeliveryStationID());
			sta.setInt(2, deliveryStation.getCityID());
			sta.setString(3, deliveryStation.getManagerID());
			sta.setString(4, deliveryStation.getStationName());
			sta.setString(5, deliveryStation.getArea());
			sta.setBoolean(6, (deliveryStation.getPrepareDelete() == true)?true:false);
			sta.setBoolean(7, (deliveryStation.getIsDelete() == true)?true:false);
			return (sta.executeUpdate()==1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * update information of deliveryStation. if update successfully, return true.
	 * @param city
	 * @return
	 */
	public Boolean update(DeliveryStation deliveryStation){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return false;
			sta = conn.prepareStatement(
 					"update DeliveryStation set cityID=? and managerID=? and "
					+ "stationName=? and area=? and prepareDelete=? and "
					+ "isDelete=? where deliveryStationID=?");	//get operation statement object of database
			sta.setInt(1, deliveryStation.getCityID());
			sta.setString(2, deliveryStation.getManagerID());
			sta.setString(3, deliveryStation.getStationName());
			sta.setString(4, deliveryStation.getArea());
			sta.setBoolean(5, (deliveryStation.getPrepareDelete() == true)?true:false);
			sta.setBoolean(6, (deliveryStation.getIsDelete() == true)?true:false);
			sta.executeUpdate();	//execute，update database
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * count amount of deliveryStations with certain attribute
	 * @param deliveryStation
	 * @return
	 */
	public Integer selectCount(DeliveryStation deliveryStation){
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = DatabaseUtil.getSelcectCountStatement(deliveryStation, conn);	//define sql query statement, then get operation statement object of database
			rs = sta.executeQuery();		//query database, return ResultSet
			if(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try { 	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
	/**
	 * the criteria query,the paging query 
	 * @param deliveryStation
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
 	public List<DeliveryStation> selectPage(DeliveryStation deliveryStation,int nowPage,int pageSize,String orderBy){
		
		List<DeliveryStation> ret = new ArrayList<DeliveryStation>();
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		if(orderBy == null)
			orderBy = "deliveryStationID";
		
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			//define sql query statement, then get operation statement object of database
			sta = DatabaseUtil.getSelectPageStatment(deliveryStation, conn, nowPage,pageSize, orderBy);	 
			rs = sta.executeQuery();		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()){
				DeliveryStation retDeliveryStation =  new DeliveryStation();
				retDeliveryStation.setDeliveryStationID(rs.getInt(1));
				retDeliveryStation.setCityID(rs.getInt(2));
				retDeliveryStation.setManagerID(rs.getString(3));
				retDeliveryStation.setStationName(rs.getString(4));
				retDeliveryStation.setArea(rs.getString(5));
				retDeliveryStation.setPrepareDelete(rs.getBoolean(6));
				retDeliveryStation.setIsDelete(rs.getBoolean(7));
				ret.add(retDeliveryStation);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
}
